package com.kaibes.orm.core.dialect;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.kaibes.core.base.util.ListUtils;
import com.kaibes.orm.core.core.ColumnField;
import com.kaibes.orm.core.core.ObjectStringConverter;
import com.kaibes.orm.core.sql.DeleteSql;
import com.kaibes.orm.core.sql.InsertContent;
import com.kaibes.orm.core.sql.InsertSql;
import com.kaibes.orm.core.sql.SelectSql;
import com.kaibes.orm.core.sql.UpdateSql;
import com.kaibes.orm.core.util.ColumnUtils;
import com.kaibes.orm.curd.Delete;
import com.kaibes.orm.curd.Insert;
import com.kaibes.orm.curd.OrderType;
import com.kaibes.orm.curd.Query;
import com.kaibes.orm.curd.Select;
import com.kaibes.orm.curd.Update;

public class MySqlDialect implements Dialect {
    
    private String tableNamePrefix;
    private ObjectStringConverter objectStringConverter;
    
    public MySqlDialect() {
    }
    
    public MySqlDialect(String tableNamePrefix, ObjectStringConverter objectStringConverter) {
        this.tableNamePrefix = tableNamePrefix;
        this.objectStringConverter = objectStringConverter;
    }

    @Override
    public InsertSql createInsertSql(Insert insert) {
        InsertSql insertSql = new InsertSql();
        Class<? extends Object> clazz = insert.getDatas().get(0).getClass();
        List<ColumnField> columnFields = ColumnUtils.getColumnFieldList(clazz);
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append("`").append(tableNamePrefix).append(insert.getTableName()).append("`");
        sb.append(" (");
        StringBuilder temp = new StringBuilder();
        temp.append("(");
        for (ColumnField key : columnFields) {
            sb.append("`").append(key.getName()).append("`,");
            temp.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") VALUES ");
        temp.deleteCharAt(temp.length() - 1);
        temp.append(")");
        InsertContent sql = new InsertContent();
        sql.setHeader(sb.toString());
        sql.setBody(temp.toString());
        insertSql.setSql(sql);
        insertSql.setDataList(insert.getDatas());
        return insertSql;
    }

    @Override
    public DeleteSql createDeleteSql(Delete delete) {
        DeleteSql deleteSql = new DeleteSql();
        List<Object> objectList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ").append("`").append(tableNamePrefix).append(delete.getTableName()).append("`");
        query(sb, objectList, delete.getQueries());
        deleteSql.setSql(sb.toString());
        deleteSql.setArgList(objectList);
        return deleteSql;
    }
    
    @Override
    public UpdateSql createUpdateSql(Update update) {
        UpdateSql updateSql = new UpdateSql();
        List<Object> objectList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ").append("`").append(tableNamePrefix).append(update.getTableName()).append("`").append(" SET ");
        mapU(sb, objectList, update.getData());
        query(sb, objectList, update.getQueries());
        updateSql.setSql(sb.toString());
        updateSql.setArgList(objectList);
        return updateSql;
    }
    
    @Override
    public SelectSql createSelectSql(Select select) {
        SelectSql selectSql = new SelectSql();
        List<Object> objectList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        if (ListUtils.isEmpty(select.getColumns())) {
            if (select.isCounted()) {
                sb.append("COUNT(*)");
            } else {
                sb.append("*");
            }
        } else {
            if (select.isCounted()) {
                sb.append("COUNT(DISTINCT ");
            }
            for (String column : select.getColumns()) {
                sb.append("`").append(column).append("`").append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            if (select.isCounted()) {
                sb.append(")");
            }
        }
        sb.append(" FROM ").append("`").append(select.getTableName()).append("`");
        // QUERY
        query(sb, objectList, select.getQueries());
        // ORDER
        orderBy(sb, select.getOrders(), select.getTypes());
        // LIMIT
        limit(sb, select.getOffset(), select.getSize());
        selectSql.setSql(sb.toString());
        selectSql.setArgList(objectList);
        return selectSql;
    }
    
    // ========================================== 
    private void query(StringBuilder sb, List<Object> objectList, List<Query> queries) {
        if (queries != null && queries.size() != 0) {
            sb.append(" WHERE ");
            int lengthO = sb.length();
            int i = 0;
            for (Query query : queries) {
                i++;
                sb.append("(");
                int length = sb.length();
                
                map(sb, objectList, query.getEq(), " = ");
                map(sb, objectList, query.getNe(), " <> ");
                map(sb, objectList, query.getGt(), " > ");
                map(sb, objectList, query.getLt(), " < ");
                map(sb, objectList, query.getGte(), " >= ");
                map(sb, objectList, query.getLte(), " <= ");
                map(sb, objectList, query.getLike(), " LIKE ");
                map(sb, objectList, query.getNotLike(), " NOT LIKE ");
                
                mapIn(sb, objectList, query.getIn(), " IN ");
                mapIn(sb, objectList, query.getNotIn(), " NOT IN ");
                
                list(sb, query.getNullValue(), " IS NULL ");
                list(sb, query.getNotNullValue(), " IS NOT NULL ");
                list(sb, query.getSqlList(), " ");
                
                toDayList(sb, query.getTodayList());

                mapSelect(sb, objectList, query.getEqSelect(), " = ");
                mapSelect(sb, objectList, query.getNeSelect(), " <> ");
                
                mapInSelect(sb, objectList, query.getInSelect(), " IN ");
                mapInSelect(sb, objectList, query.getNotInSelect(), " NOT IN ");

                if (length == sb.length()) {
                    sb.deleteCharAt(length - 1);
                } else {
                    sb.delete(sb.length() - 5, sb.length());
                    sb.append(")");
                    if (i != queries.size()) {
                        sb.append(" OR ");
                    }
                }
            }

            if (lengthO == sb.length()) {
                sb.delete(sb.length() - 7, sb.length());
            }
        }
    }
    
    private void list(StringBuilder sb, List<String> list, String sign) {
        if (list != null && !list.isEmpty()) {
            list.forEach(key->{
                if (key.contains("`")) {
                    sb.append(key).append(sign).append("AND ");
                } else {
                    sb.append("`").append(key).append("`").append(sign).append("AND ");
                }
            });
        }
    }
    
    private void toDayList(StringBuilder sb, List<String> list) {
        if (list != null && !list.isEmpty()) {
            list.forEach(key->{
                if (key.contains("`")) {
                    sb.append(key).append("=TO_DAYS(NOW()) AND ");
                } else {
                    sb.append("TO_DAYS(`").append(key).append("`)").append("=TO_DAYS(NOW()) AND ");
                }
            });
        }
    }
    
    private void map(StringBuilder sb, List<Object> objectList, Map<String, Object> map, String sign) {
        if (map != null && !map.isEmpty()) {
            map.forEach((key, object) -> {
                if (key.contains("`")) {
                    sb.append(key).append(sign).append("? AND ");
                } else {
                    sb.append("`").append(key).append("`").append(sign).append("? AND ");
                }
            });
            objectList.addAll(map.values());
        }
    }
    
    private void mapIn(StringBuilder sb, List<Object> objectList, Map<String, Object[]> map, String sign) {
        if (map != null && !map.isEmpty()) {
            map.forEach((key, values)->{
                if (values.length > 0) {
                    if (key.contains("`")) {
                        sb.append(key).append(sign).append("(");
                    } else {
                        sb.append("`").append(key).append("`").append(sign).append("(");
                    }
                    for (int i = 0; i < values.length; i++) {
                        sb.append("?,");
                    }
                    sb.deleteCharAt(sb.length() - 1);
                    sb.append(") AND ");
                    objectList.addAll(Arrays.asList(values));
                } else {
                	if (sign.length() > 4) {
                		// NOT IN
                		sb.append("TRUE AND ");
                	} else {
                		// IN
                		sb.append("FALSE AND ");
                	}
                }
            });
        }
    }
    
    private void mapSelect(StringBuilder sb, List<Object> objectList, Map<String, Select> map, String sign) {
        if (map != null && !map.isEmpty()) {
            map.forEach((key, select) -> {
                if (key.contains("`")) {
                    sb.append(key).append(sign).append("(");
                } else {
                    sb.append("`").append(key).append("`").append(sign).append("(");
                }
                String sql = select(select, objectList);
                sb.append(sql);
                sb.append(") AND ");
            });
        }
    }
    
    private void mapInSelect(StringBuilder sb, List<Object> objectList, HashMap<String, Select> map,
            String sign) {
        if (map != null && !map.isEmpty()) {
            map.forEach((key, select) -> {
                if (key.contains("`")) {
                    sb.append(key).append(sign).append("(");
                } else {
                    sb.append("`").append(key).append("`").append(sign).append("(");
                }
                String sql = select(select, objectList);
                sb.append(sql);
                sb.append(") AND ");
            });
        }
    }
    
    private void mapU(StringBuilder sb, List<Object> objectList, Map<String, Object> map) {
        if (map != null && !map.isEmpty()) {
            Set<Entry<String, Object>> entrySet = map.entrySet();
            for (Entry<String, Object> entry : entrySet) {
                String key = entry.getKey();
                if (key.endsWith("+") || key.endsWith("-") || key.endsWith("*") || key.endsWith("/")) {
                    String s = key.substring(key.length() - 1, key.length());
                    key = key.substring(0, key.length() - 1);
                    sb.append("`").append(key).append("` = ").append("`").append(key).append("`").append(s)
                    .append("?,");
                } else {
                    if (key.contains("`")) {
                        sb.append(key).append(" = ").append("?,");
                    } else {
                        sb.append("`").append(key).append("` = ").append("?,");
                    }
                }
                
                Object object = entry.getValue();
                if (object.getClass().isEnum()) {
                } else if (!isBasicClass(object.getClass())) {
                    object = objectStringConverter.object2String(object);
                    map.put(key, object);
                }
            }
            sb.deleteCharAt(sb.length() - 1);
            Collection<Object> values = map.values();
            objectList.addAll(values);
        }
    }
    
    private String select(Select select, List<Object> objectList) {
        // SELECT
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        if (ListUtils.isEmpty(select.getColumns())) {
            sb.append("*");
        } else {
            for (String column : select.getColumns()) {
                sb.append("`").append(column).append("`").append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
        }
        sb.append(" FROM ").append("`").append(select.getTableName()).append("`");
        // QUERY
        query(sb, objectList, select.getQueries());
        // ORDER
        orderBy(sb, select.getOrders(), select.getTypes());
        // LIMIT
        limit(sb, select.getOffset(), select.getSize());
        // OUT
        return sb.toString();
    }
    
    private void orderBy(StringBuilder sb, List<String> orders, List<OrderType> types) {
        if (orders == null || orders.size() == 0) {
            return;
        }
        sb.append(" ORDER BY ");
        for (int i = 0; i < orders.size(); i++) {
            sb.append("`").append(orders.get(i)).append("` ").append(types.get(i).getValue()).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
    }

    private void limit(StringBuilder sb, long offset, long size) {
        if (size == 0) {
            return;
        }
        sb.append(" LIMIT ").append(offset).append(",").append(size);
    }

    //===============
    private boolean isBasicClass(Class<?> clazz) {
        return clazz == Integer.TYPE || clazz == Integer.class || clazz == Long.TYPE || clazz == Long.class
                || clazz == String.class || clazz == BigDecimal.class || clazz == Boolean.TYPE || clazz == Boolean.class
                || clazz == Character.TYPE || clazz == Character.class || clazz == Byte.TYPE || clazz == Byte.class
                || clazz == Short.TYPE || clazz == Short.class || clazz == Float.TYPE || clazz == Float.class
                || clazz == Double.TYPE || clazz == Double.class || clazz == LocalDate.class
                || clazz == LocalDateTime.class || clazz == BigInteger.class;
    }

    private static MySqlDialect instance = new MySqlDialect();
    public static MySqlDialect getInstance() {
        return instance;
    }
}
